const express = require('express');
const Msdb = require("../../../db/Mysqldb.js");
const router = express.Router();
const multer = require('multer');
const XLSX = require('xlsx');

// 存储引擎配置
const memoryStorage = multer.memoryStorage();
const diskStorage = multer({ storage: multer.diskStorage({}) });

// 动态选择存储引擎中间件
const dynamicUpload = (req, res, next) => {
  const storage = req.body.type ? diskStorage : memoryStorage;
  multer({ storage, limits: { fileSize: 100 * 1024 * 1024 } }).single('file')(req, res, next);
};

const db = new Msdb();
// 获取格式化时间
const getTime = () => 
  new Date(Date.now() + 8 * 60 * 60 * 1000).toISOString().slice(0, 19).replace('T', ' ');

// 解析Excel为JSON
const parseExcel = (buffer, dateNF) => {
  const workbook = XLSX.read(buffer, { cellStyles: true, cellDates: false, type: 'buffer' });
  const sheetName = workbook.SheetNames[0];
  return XLSX.utils.sheet_to_json(workbook.Sheets[sheetName], { raw: false, dateNF });
};

// 批量新增销售单
router.post("/addSalesByExcel", dynamicUpload, async (req, res) => {
  const connection = await db.getConnection();
  const result = {
    code: 200,
    msg: "新增成功",
    data: { 
      ok: 0,         // 成功条数
      no: 0,         // 失败条数
      warning: 0,    // 警告条数（如引用异常但仍导入）
      reasons: [],   // 失败原因
      warnings: [],  // 警告原因
      successOrders: [] // 成功导入的订单号列表（用于日志）
    }
  };

  if (!req.file) return res.status(400).send({ code: 400, msg: "未上传文件" });

  try {
    const jsonData = parseExcel(req.file.buffer, 'yyyy-mm-dd');
    const total = jsonData.length; // 总记录数
    const returnNull = {
      订单号: null, 产品料号: null, 所属客户: null, 订单日期: null, 交货日期: null,
      规格型号: null, 数量: null, 金额: null, 特殊备注: null, 商务合同地址: null,
      技术协议地址: null, 商务合同引用单号: null, 技术协议引用单号: null
    };
    let insertData = returnNull;

    await db.beginTransaction(connection);

    for (let i = 0; i < jsonData.length; i++) {
      insertData = { ...returnNull, ...jsonData[i] };
      const row = i + 2; // Excel行号（从第2行开始）

      // 必填项检查
      if (!insertData.订单号) {
        result.data.no++;
        result.data.reasons.push({ row, reason: "订单号缺失" });
        continue;
      }
      if (!insertData.产品料号) {
        result.data.no++;
        result.data.reasons.push({ row, reason: "产品料号缺失" });
        continue;
      }
      if (!insertData.所属客户) {
        result.data.no++;
        result.data.reasons.push({ row, reason: "所属客户缺失" });
        continue;
      }

      // 订单号重复检查
      const [orders] = await connection.execute(
        `SELECT id FROM sales_order WHERE order_id=?`, [insertData.订单号]
      );
      if (orders.length) {
        result.data.no++;
        result.data.reasons.push({ row, reason: "订单号重复" });
        continue;
      }

      // 引用单号处理
      let warning = false, msg = "";
      // 商务合同引用
      if (!insertData['商务合同地址'] && insertData['商务合同引用单号']) {
        const [ref] = await connection.execute(
          `SELECT business_contract FROM sales_order WHERE order_id = ?`,
          [insertData['商务合同引用单号']]
        );
        if (ref.length && ref[0].business_contract) {
          insertData['商务合同地址'] = ref[0].business_contract;
        } else {
          warning = true;
          msg += (msg ? '；' : '') + "商务合同引用单号无效";
        }
      }
      // 技术协议引用
      if (!insertData['技术协议地址'] && insertData['技术协议引用单号']) {
        const [ref] = await connection.execute(
          `SELECT technical_agreement FROM sales_order WHERE order_id = ?`,
          [insertData['技术协议引用单号']]
        );
        if (ref.length && ref[0].technical_agreement) {
          insertData['技术协议地址'] = ref[0].technical_agreement;
        } else {
          warning = true;
          msg += (msg ? '；' : '') + "技术协议引用单号无效";
        }
      }

      // 插入数据
      await connection.execute(
        `INSERT INTO sales_order (order_id,product_part_number,customer,order_date,delivery_date,specification_and_model,
        number,money,remark,business_contract,technical_agreement,insert_user_id,create_time) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)`,
        [insertData['订单号'], insertData['产品料号'], insertData['所属客户'], insertData['订单日期'],
        insertData['交货日期'], insertData['规格型号'], insertData['数量']?.trim() || null,
        insertData['金额']?.trim() || null, insertData['特殊备注'], insertData['商务合同地址'],
        insertData['技术协议地址'], req.auth.user_id, getTime()]
      );

      // 更新结果（记录成功的订单号）
      result.data.successOrders.push(insertData['订单号']);
      if (warning) {
        result.data.warning++;
        result.data.warnings.push({ row, reason: msg });
      } else {
        result.data.ok++;
      }
    }

    // 生成批量导入日志（核心优化点）
    if (result.data.ok || result.data.warning || result.data.no) {
      // 1. 处理成功订单号（避免大量数据导致日志过长）
      let successOrdersLog = '';
      if (result.data.successOrders.length > 0) {
        if (result.data.successOrders.length <= 10) {
          successOrdersLog = `成功订单号: ${result.data.successOrders.join(', ')}`;
        } else {
          // 超过10条时显示范围（前3+后3）
          const first3 = result.data.successOrders.slice(0, 3).join(', ');
          const last3 = result.data.successOrders.slice(-3).join(', ');
          successOrdersLog = `成功订单号（共${result.data.successOrders.length}条）: ${first3}, ... , ${last3}`;
        }
      }

      // 2. 失败和警告原因汇总
      const failReasons = result.data.reasons
        .map(item => `${item.row}行:${item.reason}`)
        .slice(0, 5) // 只显示前5条失败原因
        .join('; ');
      const warningReasons = result.data.warnings
        .map(item => `${item.row}行:${item.reason}`)
        .slice(0, 5) // 只显示前5条警告原因
        .join('; ');

      // 3. 组合完整日志信息
      const logMessage = `用户ID: ${req.auth.user_id} 通过Excel批量导入销售单。` +
        `总记录数: ${total}条; 成功: ${result.data.ok}条; 警告: ${result.data.warning}条; 失败: ${result.data.no}条。` +
        (successOrdersLog ? ` ${successOrdersLog};` : '') +
        (result.data.no > 0 ? ` 失败原因（前5条）: ${failReasons || '无'};` : '') +
        (result.data.warning > 0 ? ` 警告原因（前5条）: ${warningReasons || '无'};` : '');

      // 4. 插入日志
      await connection.execute(
        `INSERT INTO operation_log (user_id, sql_words, time) VALUES (?, ?, ?)`,
        [req.auth.user_id, logMessage, getTime()]
      );
    }

    await db.commit(connection);
    res.send(result);
  } catch (error) {
    await db.rollback(connection);
    res.status(500).send({ code: 500, msg: "Excel解析失败", error: error.message });
  } finally {
    connection.release();
  }
});

// 批量新增产品出货单
router.post("/addProductShipmentByExcel", dynamicUpload, async (req, res) => {
  const connection = await db.getConnection();
  const result = {
    code: 200,
    msg: "新增成功",
    data: { 
      ok: 0,         // 成功条数
      no: 0,         // 失败条数
      warning: 0,    // 警告条数
      reasons: [],   // 失败原因
      warnings: [],  // 警告原因
      successShipments: [] // 成功导入的出货单号列表（用于日志）
    }
  };

  if (!req.file) return res.status(400).send({ code: 400, msg: "未上传文件" });

  try {
    const jsonData = parseExcel(req.file.buffer, 'yyyy-mm-dd');
    const total = jsonData.length; // 总记录数
    const returnNull = {
      出货单号: null, 送货地址: null, 产品料号: null, 产品名称: null, 产品规格: null,
      出货数量: null, 出货日期: null, 出货客户: null, 销售单号: null, 交货清单地址: null,
      出厂验收报告地址: null, 出货计划地址: null, 产品出货单地址: null, 客户验收报告地址: null,
      交货清单引用单号: null, 出厂验收报告引用单号: null, 出货计划引用单号: null,
      产品出货单引用单号: null, 客户验收报告引用单号: null
    };

    await db.beginTransaction(connection);

    for (let i = 0; i < jsonData.length; i++) {
      const insertData = { ...returnNull, ...jsonData[i] };
      const row = i + 2;

      // 必填项检查
      if (!insertData.出货单号) {
        result.data.no++;
        result.data.reasons.push({ row, reason: "出货单号缺失" });
        continue;
      }
      if (!insertData.送货地址) {
        result.data.no++;
        result.data.reasons.push({ row, reason: "送货地址缺失" });
        continue;
      }
      if (!insertData.产品料号) {
        result.data.no++;
        result.data.reasons.push({ row, reason: "产品料号缺失" });
        continue;
      }

      // 出货单号重复检查
      const [orders] = await connection.execute(
        `SELECT id FROM product_shipment_order WHERE shipment_order_id=?`, [insertData.出货单号]
      );
      if (orders.length) {
        result.data.no++;
        result.data.reasons.push({ row, reason: "出货单号重复" });
        continue;
      }

      // 引用单号处理
      let warning = false, msg = "";
      // 交货清单引用
      if (!insertData['交货清单地址'] && insertData['交货清单引用单号']) {
        const [ref] = await connection.execute(
          `SELECT delivery_list FROM product_shipment_order WHERE shipment_order_id = ?`,
          [insertData['交货清单引用单号']]
        );
        if (ref.length && ref[0].delivery_list) {
          insertData['交货清单地址'] = ref[0].delivery_list;
        } else {
          warning = true;
          msg += (msg ? '；' : '') + "交货清单引用单号无效";
        }
      }
      // 出厂验收报告引用
      if (!insertData['出厂验收报告地址'] && insertData['出厂验收报告引用单号']) {
        const [ref] = await connection.execute(
          `SELECT factory_acceptance_report FROM product_shipment_order WHERE shipment_order_id = ?`,
          [insertData['出厂验收报告引用单号']]
        );
        if (ref.length && ref[0].factory_acceptance_report) {
          insertData['出厂验收报告地址'] = ref[0].factory_acceptance_report;
        } else {
          warning = true;
          msg += (msg ? '；' : '') + "出厂验收报告引用单号无效";
        }
      }
      // 出货计划引用
      if (!insertData['出货计划地址'] && insertData['出货计划引用单号']) {
        const [ref] = await connection.execute(
          `SELECT shipment_plan FROM product_shipment_order WHERE shipment_order_id = ?`,
          [insertData['出货计划引用单号']]
        );
        if (ref.length && ref[0].shipment_plan) {
          insertData['出货计划地址'] = ref[0].shipment_plan;
        } else {
          warning = true;
          msg += (msg ? '；' : '') + "出货计划引用单号无效";
        }
      }
      // 产品出货单引用
      if (!insertData['产品出货单地址'] && insertData['产品出货单引用单号']) {
        const [ref] = await connection.execute(
          `SELECT product_shipment_order FROM product_shipment_order WHERE shipment_order_id = ?`,
          [insertData['产品出货单引用单号']]
        );
        if (ref.length && ref[0].product_shipment_order) {
          insertData['产品出货单地址'] = ref[0].product_shipment_order;
        } else {
          warning = true;
          msg += (msg ? '；' : '') + "产品出货单引用单号无效";
        }
      }
      // 客户验收报告引用
      if (!insertData['客户验收报告地址'] && insertData['客户验收报告引用单号']) {
        const [ref] = await connection.execute(
          `SELECT customer_acceptance_report FROM product_shipment_order WHERE shipment_order_id = ?`,
          [insertData['客户验收报告引用单号']]
        );
        if (ref.length && ref[0].customer_acceptance_report) {
          insertData['客户验收报告地址'] = ref[0].customer_acceptance_report;
        } else {
          warning = true;
          msg += (msg ? '；' : '') + "客户验收报告引用单号无效";
        }
      }

      // 插入数据
      await connection.execute(
        `INSERT INTO product_shipment_order (shipment_order_id,delivery_address,product_part_number,product_name,
        specification,shipment_number,shipment_date,shipment_customer,sales_order_id,delivery_list,factory_acceptance_report,
        shipment_plan,product_shipment_order,customer_acceptance_report,insert_user_id,create_time) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
        [insertData['出货单号'], insertData['送货地址'], insertData['产品料号'], insertData['产品名称'],
        insertData['产品规格'], insertData['出货数量']?.trim() || null, insertData['出货日期'],
        insertData['出货客户'], insertData['销售单号'], insertData['交货清单地址'], insertData['出厂验收报告地址'],
        insertData['出货计划地址'], insertData['产品出货单地址'], insertData['客户验收报告地址'],
        req.auth.user_id, getTime()]
      );

      // 更新结果（记录成功的出货单号）
      result.data.successShipments.push(insertData['出货单号']);
      if (warning) {
        result.data.warning++;
        result.data.warnings.push({ row, reason: msg });
      } else {
        result.data.ok++;
      }
    }

    // 生成批量导入日志（核心优化点）
    if (result.data.ok || result.data.warning || result.data.no) {
      // 1. 处理成功出货单号（避免大量数据导致日志过长）
      let successShipmentsLog = '';
      if (result.data.successShipments.length > 0) {
        if (result.data.successShipments.length <= 10) {
          successShipmentsLog = `成功出货单号: ${result.data.successShipments.join(', ')}`;
        } else {
          // 超过10条时显示范围（前3+后3）
          const first3 = result.data.successShipments.slice(0, 3).join(', ');
          const last3 = result.data.successShipments.slice(-3).join(', ');
          successShipmentsLog = `成功出货单号（共${result.data.successShipments.length}条）: ${first3}, ... , ${last3}`;
        }
      }

      // 2. 失败和警告原因汇总
      const failReasons = result.data.reasons
        .map(item => `${item.row}行:${item.reason}`)
        .slice(0, 5) // 只显示前5条失败原因
        .join('; ');
      const warningReasons = result.data.warnings
        .map(item => `${item.row}行:${item.reason}`)
        .slice(0, 5) // 只显示前5条警告原因
        .join('; ');

      // 3. 组合完整日志信息
      const logMessage = `用户ID: ${req.auth.user_id} 通过Excel批量导入产品出货单。` +
        `总记录数: ${total}条; 成功: ${result.data.ok}条; 警告: ${result.data.warning}条; 失败: ${result.data.no}条。` +
        (successShipmentsLog ? ` ${successShipmentsLog};` : '') +
        (result.data.no > 0 ? ` 失败原因（前5条）: ${failReasons || '无'};` : '') +
        (result.data.warning > 0 ? ` 警告原因（前5条）: ${warningReasons || '无'};` : '');

      // 4. 插入日志
      await connection.execute(
        `INSERT INTO operation_log (user_id, sql_words, time) VALUES (?, ?, ?)`,
        [req.auth.user_id, logMessage, getTime()]
      );
    }

    await db.commit(connection);
    res.send(result);
  } catch (error) {
    await db.rollback(connection);
    res.status(500).send({ code: 500, msg: "Excel解析失败", error: error.message });
  } finally {
    connection.release();
  }
});

// 批量新增售后服务单
router.post("/addAfterSalesByExcel", dynamicUpload, async (req, res) => {
  const connection = await db.getConnection();
  const result = {
    code: 200,
    msg: "新增成功",
    data: { 
      ok: 0,         // 成功条数
      no: 0,         // 失败条数
      warning: 0,    // 警告条数
      reasons: [],   // 失败原因
      warnings: [],  // 警告原因
      successServices: [] // 成功导入的服务单号列表（用于日志）
    }
  };

  if (!req.file) return res.status(400).send({ code: 400, msg: "未上传文件" });

  try {
    const jsonData = parseExcel(req.file.buffer, 'yyyy-mm-dd hh:mm:ss');
    const total = jsonData.length; // 总记录数
    const returnNull = {
      服务单号: null, 服务时间: null, 对应产品SN: null, 是否维保内: null, 对应合同号: null,
      对应销售订单号: null, 售后人员: null, 服务费用: null, 服务工时: null, 需要替换物料: null,
      问题描述清单地址: null, 问题解决方案地址: null, 问题描述清单引用单号: null, 问题解决方案引用单号: null
    };

    await db.beginTransaction(connection);

    for (let i = 0; i < jsonData.length; i++) {
      const insertData = { ...returnNull, ...jsonData[i] };
      const row = i + 2;

      // 必填项检查
      if (!insertData.服务单号) {
        result.data.no++;
        result.data.reasons.push({ row, reason: "服务单号缺失" });
        continue;
      }
      if (!insertData.对应产品SN) {
        result.data.no++;
        result.data.reasons.push({ row, reason: "对应产品SN缺失" });
        continue;
      }
      if (insertData.是否维保内 === null) {
        result.data.no++;
        result.data.reasons.push({ row, reason: "是否维保内缺失" });
        continue;
      }

      // 服务单号重复检查
      const [orders] = await connection.execute(
        `SELECT id FROM after_sales_order WHERE service_order_id=?`, [insertData.服务单号]
      );
      if (orders.length) {
        result.data.no++;
        result.data.reasons.push({ row, reason: "服务单号重复" });
        continue;
      }

      // 业务逻辑处理
      let warning = false, msg = "";
      // 售后人员验证
      if (insertData.售后人员) {
        const [users] = await connection.execute(
          `SELECT id FROM user WHERE name=? AND role_id=9`, [insertData.售后人员]
        );
        if (users.length === 0) {
          warning = true;
          msg += "该人员不存在于售后，请手动查询确认！";
          insertData.售后人员 = null;
        } else if (users.length > 1) {
          warning = true;
          msg += "该人员名称在售后人员中存在多人，请手动查询确认！";
          insertData.售后人员 = null;
        } else {
          insertData.售后人员 = users[0].id;
        }
      }
      // 问题描述清单引用
      if (!insertData['问题描述清单地址'] && insertData['问题描述清单引用单号']) {
        const [ref] = await connection.execute(
          `SELECT problem_description_list FROM after_sales_order WHERE service_order_id = ?`,
          [insertData['问题描述清单引用单号']]
        );
        if (ref.length && ref[0].problem_description_list) {
          insertData['问题描述清单地址'] = ref[0].problem_description_list;
        } else {
          warning = true;
          msg += (msg ? '；' : '') + "问题描述清单引用单号无效";
        }
      }
      // 问题解决方案引用
      if (!insertData['问题解决方案地址'] && insertData['问题解决方案引用单号']) {
        const [ref] = await connection.execute(
          `SELECT problem_solving_solution FROM after_sales_order WHERE service_order_id = ?`,
          [insertData['问题解决方案引用单号']]
        );
        if (ref.length && ref[0].problem_solving_solution) {
          insertData['问题解决方案地址'] = ref[0].problem_solving_solution;
        } else {
          warning = true;
          msg += (msg ? '；' : '') + "问题解决方案引用单号无效";
        }
      }

      // 插入数据
      await connection.execute(
        `INSERT INTO after_sales_order (service_order_id,service_time,sn,is_mw,contract_number,sales_order_id,after_sales_person_id,
        money,time,need_bom_number,problem_description_list,problem_solving_solution,insert_user_id,create_time) 
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
        [insertData['服务单号'], insertData['服务时间'], insertData['对应产品SN'],
        insertData['是否维保内'] === '是' ? 1 : 0, insertData['对应合同号'], insertData['对应销售订单号'],
        insertData['售后人员'], insertData['服务费用']?.trim() || null,
        insertData['服务工时']?.trim() || null, insertData['需要替换物料'],
        insertData['问题描述清单地址'], insertData['问题解决方案地址'], req.auth.user_id, getTime()]
      );

      // 更新结果（记录成功的服务单号）
      result.data.successServices.push(insertData['服务单号']);
      if (warning) {
        result.data.warning++;
        result.data.warnings.push({ row, reason: msg });
      } else {
        result.data.ok++;
      }
    }

    // 生成批量导入日志（核心优化点）
    if (result.data.ok || result.data.warning || result.data.no) {
      // 1. 处理成功服务单号（避免大量数据导致日志过长）
      let successServicesLog = '';
      if (result.data.successServices.length > 0) {
        if (result.data.successServices.length <= 10) {
          successServicesLog = `成功服务单号: ${result.data.successServices.join(', ')}`;
        } else {
          // 超过10条时显示范围（前3+后3）
          const first3 = result.data.successServices.slice(0, 3).join(', ');
          const last3 = result.data.successServices.slice(-3).join(', ');
          successServicesLog = `成功服务单号（共${result.data.successServices.length}条）: ${first3}, ... , ${last3}`;
        }
      }

      // 2. 失败和警告原因汇总
      const failReasons = result.data.reasons
        .map(item => `${item.row}行:${item.reason}`)
        .slice(0, 5) // 只显示前5条失败原因
        .join('; ');
      const warningReasons = result.data.warnings
        .map(item => `${item.row}行:${item.reason}`)
        .slice(0, 5) // 只显示前5条警告原因
        .join('; ');

      // 3. 组合完整日志信息
      const logMessage = `用户ID: ${req.auth.user_id} 通过Excel批量导入售后服务单。` +
        `总记录数: ${total}条; 成功: ${result.data.ok}条; 警告: ${result.data.warning}条; 失败: ${result.data.no}条。` +
        (successServicesLog ? ` ${successServicesLog};` : '') +
        (result.data.no > 0 ? ` 失败原因（前5条）: ${failReasons || '无'};` : '') +
        (result.data.warning > 0 ? ` 警告原因（前5条）: ${warningReasons || '无'};` : '');

      // 4. 插入日志
      await connection.execute(
        `INSERT INTO operation_log (user_id, sql_words, time) VALUES (?, ?, ?)`,
        [req.auth.user_id, logMessage, getTime()]
      );
    }

    await db.commit(connection);
    res.send(result);
  } catch (error) {
    await db.rollback(connection);
    console.error("Excel解析失败:", error);
    res.status(500).send({ code: 500, msg: "Excel解析失败", error: error.message });
  } finally {
    connection.release();
  }
});

// 批量新增用户
router.post("/addUserByExcel", dynamicUpload, async (req, res) => {
  const connection = await db.getConnection();
  const result = {
    code: 200,
    msg: "新增成功",
    data: { 
      ok: 0,         // 成功条数
      no: 0,         // 失败条数
      warning: 0,    // 警告条数
      reasons: [],   // 失败原因
      warnings: [],  // 警告原因
      successUsers: [] // 成功导入的用户工号列表（用于日志）
    }
  };

  if (!req.file) return res.status(400).send({ code: 400, msg: "未上传文件" });

  try {
    const jsonData = parseExcel(req.file.buffer, 'yyyy-mm-dd hh:mm:ss');
    const total = jsonData.length; // 总记录数
    const returnNull = { 工号: null, 姓名: null, 角色名称: null, 密码: null, 电话号码: null };

    await db.beginTransaction(connection);

    for (let i = 0; i < jsonData.length; i++) {
      const insertData = { ...returnNull, ...jsonData[i] };
      const row = i + 2;

      // 工号检查
      if (!insertData.工号) {
        result.data.no++;
        result.data.reasons.push({ row, reason: "工号缺失" });
        continue;
      }
      const [users] = await connection.execute(
        `SELECT id FROM user WHERE work_number=?`, [insertData.工号]
      );
      if (users.length) {
        result.data.no++;
        result.data.reasons.push({ row, reason: "工号重复" });
        continue;
      }

      // 业务逻辑处理
      let warning = false, msg = "";
      // 角色验证
      if (insertData.角色名称) {
        const [roles] = await connection.execute(
          `SELECT id FROM role WHERE role_name=?`, [insertData.角色名称]
        );
        if (roles.length === 0) {
          warning = true;
          msg += "该角色不存在，请手动查询确认！";
          insertData.角色名称 = 2; // 默认用户
        } else {
          insertData.角色名称 = roles[0].id;
        }
      }
      // 密码处理
      if (insertData.密码) {
        if (insertData.密码.length < 6 || insertData.密码.length > 20) {
          warning = true;
          msg += (msg ? '；' : '') + "密码长度必须为6-20位，已自动设置为默认密码\"654321\"";
          insertData.密码 = "654321";
        }
      } else {
        warning = true;
        msg += (msg ? '；' : '') + "未填写密码，已自动设置为默认密码\"654321\"";
        insertData.密码 = "654321";
      }
      // 电话号码验证
      if (insertData.电话号码 && !/^1\d{10}$/.test(insertData.电话号码)) {
        warning = true;
        msg += (msg ? '；' : '') + "电话号码格式错误（需为11位数字），已自动清空";
        insertData.电话号码 = null;
      }

      // 插入数据
      await connection.execute(
        `INSERT INTO user (account,password,name,role_id,phone,work_number,create_time,insert_user_id) 
        VALUES (?,?,?,?,?,?,?,?)`,
        [insertData['工号'], insertData['密码'], insertData['姓名'],
        insertData['角色名称'], insertData['电话号码'], insertData['工号'],
        getTime(), req.auth.user_id]
      );

      // 更新结果（记录成功的用户工号）
      result.data.successUsers.push({
        workNumber: insertData['工号'],
        name: insertData['姓名']
      });
      if (warning) {
        result.data.warning++;
        result.data.warnings.push({ row, reason: msg });
      } else {
        result.data.ok++;
      }
    }

    // 生成批量导入日志（核心优化点）
    if (result.data.ok || result.data.warning || result.data.no) {
      // 1. 处理成功用户信息（避免大量数据导致日志过长）
      let successUsersLog = '';
      if (result.data.successUsers.length > 0) {
        if (result.data.successUsers.length <= 10) {
          successUsersLog = `成功用户: ${result.data.successUsers.map(u => `${u.name}(${u.workNumber})`).join(', ')}`;
        } else {
          // 超过10条时显示范围（前3+后3）
          const first3 = result.data.successUsers.slice(0, 3).map(u => `${u.name}(${u.workNumber})`).join(', ');
          const last3 = result.data.successUsers.slice(-3).map(u => `${u.name}(${u.workNumber})`).join(', ');
          successUsersLog = `成功用户（共${result.data.successUsers.length}条）: ${first3}, ... , ${last3}`;
        }
      }

      // 2. 失败和警告原因汇总
      const failReasons = result.data.reasons
        .map(item => `${item.row}行:${item.reason}`)
        .slice(0, 5) // 只显示前5条失败原因
        .join('; ');
      const warningReasons = result.data.warnings
        .map(item => `${item.row}行:${item.reason}`)
        .slice(0, 5) // 只显示前5条警告原因
        .join('; ');

      // 3. 组合完整日志信息
      const logMessage = `用户ID: ${req.auth.user_id} 通过Excel批量导入用户。` +
        `总记录数: ${total}条; 成功: ${result.data.ok}条; 警告: ${result.data.warning}条; 失败: ${result.data.no}条。` +
        (successUsersLog ? ` ${successUsersLog};` : '') +
        (result.data.no > 0 ? ` 失败原因（前5条）: ${failReasons || '无'};` : '') +
        (result.data.warning > 0 ? ` 警告原因（前5条）: ${warningReasons || '无'};` : '');

      // 4. 插入日志
      await connection.execute(
        `INSERT INTO operation_log (user_id, sql_words, time) VALUES (?, ?, ?)`,
        [req.auth.user_id, logMessage, getTime()]
      );
    }

    await db.commit(connection);
    res.send(result);
  } catch (error) {
    await db.rollback(connection);
    res.status(500).send({ code: 500, msg: "Excel解析失败", error: error.message });
  } finally {
    connection.release();
  }
});

// 批量新增BOM
router.post("/addBomByExcel", dynamicUpload, async (req, res) => {
  const connection = await db.getConnection();
  const result = {
    code: 200,
    msg: "新增成功",
    data: { 
      ok: 0,         // 成功条数
      no: 0,         // 失败条数
      warning: 0,    // 警告条数
      reasons: [],   // 失败原因
      warnings: [],  // 警告原因
      successBoms: [] // 成功导入的BOM编号列表（用于日志）
    }
  };

  if (!req.file) return res.status(400).send({ code: 400, msg: "未上传文件" });

  try {
    // 解析Excel，假设日期格式不需要特殊处理
    const jsonData = parseExcel(req.file.buffer);
    const total = jsonData.length; // 总记录数
    
    // 定义默认空值对象，对应Excel表头
    const returnNull = { 
      '产品编号': null, 
      '上级产品编号': "", //默认空字符串
      '产品品名': null, 
      '产品规格': null 
    };

    await db.beginTransaction(connection);

    for (let i = 0; i < jsonData.length; i++) {
      var warning=false;
      // 合并数据，确保所有字段都存在
      const insertData = { ...returnNull, ...jsonData[i] };
      const row = i + 2; // Excel行号从2开始（表头是1行）

      // 产品编号检查（必填项）
      if (!insertData['产品编号']) {
        result.data.no++;
        result.data.reasons.push({ row, reason: "产品编号缺失" });
        continue;
      }
      
      // 检查产品编号是否已存在
      const [boms] = await connection.execute(
        `SELECT id FROM bom WHERE number=?`, [insertData['产品编号']]
      );
      if (boms.length) {
        result.data.no++;
        result.data.reasons.push({ row, reason: `产品编号${insertData['产品编号']}已存在` });
        continue;
      }
      
      // 检查上级产品编号是否存在
      if(insertData['上级产品编号']){
        const [parentBoms] = await connection.execute(
          `SELECT id FROM bom WHERE number=?`, [insertData['上级产品编号']]
        );
        if (parentBoms.length === 0) {
          result.data.no++;
          result.data.reasons.push({ row, reason: `上级产品编号不存在` });
          continue;
        }
      }

      // 产品品名检查
      if (!insertData['产品品名']) {
        result.data.no++;
        result.data.reasons.push({ row, reason: "产品品名缺失" });
        continue;
      }

      // 产品规格检查
      if (!insertData['产品规格']) {
        result.data.no++;
        result.data.reasons.push({ row, reason: "产品规格缺失" });
        continue;
      }

      // 插入数据到BOM表
      await connection.execute(
        `INSERT INTO bom (number, parent_number, product_name, specification_and_model, create_time, insert_user_id) 
        VALUES (?, ?, ?, ?, ?, ?)`,
        [
          insertData['产品编号'],
          insertData['上级产品编号'],
          insertData['产品品名'],
          insertData['产品规格'],
          getTime(),
          req.auth.user_id
        ]
      );

      // 更新结果（记录成功的BOM编号）
      result.data.successBoms.push({
        number: insertData['产品编号'],
        name: insertData['产品品名']
      });
      
      if (warning) {
        result.data.warning++;
        result.data.warnings.push({ row, reason: msg });
      } else {
        result.data.ok++;
      }
    }

    // 生成批量导入日志
    if (result.data.ok || result.data.warning || result.data.no) {
      // 1. 处理成功BOM信息
      let successBomsLog = '';
      if (result.data.successBoms.length > 0) {
        if (result.data.successBoms.length <= 10) {
          successBomsLog = `成功BOM: ${result.data.successBoms.map(b => `${b.name}(${b.number})`).join(', ')}`;
        } else {
          // 超过10条时显示范围（前3+后3）
          const first3 = result.data.successBoms.slice(0, 3).map(b => `${b.name}(${b.number})`).join(', ');
          const last3 = result.data.successBoms.slice(-3).map(b => `${b.name}(${b.number})`).join(', ');
          successBomsLog = `成功BOM（共${result.data.successBoms.length}条）: ${first3}, ... , ${last3}`;
        }
      }

      // 2. 失败和警告原因汇总
      const failReasons = result.data.reasons
        .map(item => `${item.row}行:${item.reason}`)
        .slice(0, 5) // 只显示前5条失败原因
        .join('; ');
      const warningReasons = result.data.warnings
        .map(item => `${item.row}行:${item.reason}`)
        .slice(0, 5) // 只显示前5条警告原因
        .join('; ');

      // 3. 组合完整日志信息
      const logMessage = `用户ID: ${req.auth.user_id} 通过Excel批量导入BOM。` +
        `总记录数: ${total}条; 成功: ${result.data.ok}条; 警告: ${result.data.warning}条; 失败: ${result.data.no}条。` +
        (successBomsLog ? ` ${successBomsLog};` : '') +
        (result.data.no > 0 ? ` 失败原因（前5条）: ${failReasons || '无'};` : '') +
        (result.data.warning > 0 ? ` 警告原因（前5条）: ${warningReasons || '无'};` : '');

      // 4. 插入日志
      await connection.execute(
        `INSERT INTO operation_log (user_id, sql_words, time) VALUES (?, ?, ?)`,
        [req.auth.user_id, logMessage, getTime()]
      );
    }

    await db.commit(connection);
    res.send(result);
  } catch (error) {
    await db.rollback(connection);
    res.status(500).send({ code: 500, msg: "Excel解析失败", error: error.message });
  } finally {
    connection.release();
  }
});


module.exports = router;